﻿CREATE FUNCTION [dbo].[fs_Repo_Base](
	@StateID int
)RETURNS TABLE
AS

RETURN 
SELECT
L_B.LoanID,
CF.UsixID,
C.AgentUserName Agent,
L_B.FirstName AS [First Name],
L_B.LastName AS [Last Name], 
CONVERT(varchar,C.StatusDate,111) AS [Date In],
DATEDIFF(dd,C.StatusDate,GETDATE()) AS [Days In],
Status AS LoanStatus
FROM v_List_Base L_B
INNER JOIN t_Repossession C ON C.LoanID = L_B.LoanID
LEFT OUTER JOIN usix.t_CustomerFile_Export AS CF ON C.LoanID = CF.LoanID
INNER JOIN dbo.fs_Repo_Read(DEFAULT, @StateID) AS fs_Loans
	ON L_B.LoanID = fs_Loans.LoanID
/*
INNER JOIN
                          (SELECT     LoanID, MenuID, COUNT(*) AS Count
                            FROM          t_Loan_Menus
                            GROUP BY LoanID, MenuID
                            HAVING      (MenuID = @MenuID)) AS MENU_COUNT ON L_B.LoanID = MENU_COUNT.LoanID LEFT OUTER JOIN
                          (SELECT     LoanID, MAX(DateStamp) AS DateStamp
                            FROM          t_Notes AS t_Notes_1
                            GROUP BY LoanID) AS t_Notes ON L_B.LoanID = t_Notes.LoanID
*/




